package com.codingman.jdbcdemo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

//CRUD
public class Demo4 {

	public static Connection getConnection() throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.cj.jdbc.Driver");

		return DriverManager.getConnection("jdbc:mysql://45.77.187.28:3306/codingdb", "codingman",
				"Cm@123456");
	}

	public static void insert() {
		try {
			Connection con = getConnection();

			PreparedStatement stmt = con.prepareStatement("insert into employee values(?,?,?)");
			stmt.setInt(1, 101);
			stmt.setString(2, "Candy");
			stmt.setFloat(3, 200);

			int i = stmt.executeUpdate();
			System.out.println(i + " records inserted");

		} catch (SQLException | ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public static void update() {
		try {
			Connection con = getConnection();

			PreparedStatement stmt = con.prepareStatement("update employee set name=? where id=?");
			stmt.setString(1, "Bob");
			stmt.setInt(2, 102);

			int i = stmt.executeUpdate();
			System.out.println(i + " records updated");

		} catch (SQLException | ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public static void delete() {
		try {
			Connection con = getConnection();

			PreparedStatement stmt = con.prepareStatement("delete from employee where id=?");
			stmt.setInt(1, 102);

			int i = stmt.executeUpdate();
			System.out.println(i + " records deleted");

		} catch (SQLException | ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public static void query() {
		try {
			Connection con = getConnection();

			PreparedStatement stmt = con.prepareStatement("select * from employee");
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				System.out.println(rs.getInt("id") + " " + rs.getString("name") + " " + rs.getFloat("salary"));
			}
		} catch (SQLException | ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {
//		insert();
//		update();
//		delete();
		query();
	}

}
